
'''
    sql module
    使用python连接java程序，java去连接数据库
    （还可以使用gsql命令，-c 或者 -f 参数携带sql语句）

'''
import socket
import json

from yat.test import Node
from yat.test import macro

from testcase.utils.Logger import Logger

logger = Logger()


class PgSql():

    def __init__(self, database, node='dbuser'):
        '''
        初始化数据库连接
        psql = PgSql(database, dbconfigname), database为要连接的数据库名称。
        dbconfigname默认使用 dbuser(见conf/nodes.yml)
        '''
        db_node = Node(node=node)
        self.databaseConfig = {
            'user':  db_node.db_user,
            'port': db_node.db_port,
            'host': db_node.db_host,
            'password': db_node.db_password,
            'database': database
        }
        self.db_node = db_node
        self.init_socket_connection()
        logger.error(self.databaseConfig)
        self.create_database(database)
        self.startTransaction = 'false'

    
    def create_database(self, database):
        '''
        创建数据库，使用gsql命令中 -c 参数携带sql语句
        '''
        createshell = '''
        source /home/{sshDbUser}/gaussdb.bashrc;
        gsql -d postgres -p {port} -r -c "create database {database};"
        '''.format(sshDbUser=self.db_node.ssh_user,
            port=self.db_node.db_port, database=database)
        msg = self.db_node.sh(createshell).result()
        logger.info('create database ' + database)
        return msg

    
    def drop_database(self, database):
        '''
        删除数据库
        '''
        dropshell = '''
        source /home/{sshDbUser}/gaussdb.bashrc;
        gsql -d postgres -p {port} -r -c "drop database if exists {database};"
        '''.format(sshDbUser=self.db_node.ssh_user,
            port=self.db_node.db_port, database=database)
        msg = self.db_node.sh(dropshell).result()
        logger.info('drop database ' + database)
        return msg


    def executer_query(self, sql):
        '''
        适用于查询语句，有返回结果。
        返回结果为二维数据
        '''
        logger.info(sql)
        return self.connect_and_send(sql, 'query')


    def executer_insert(self, sql):
        '''
        表格插入数据
        '''
        logger.info(sql)
        return self.connect_and_send(sql, 'insert')


    def executer_update(self, sql):
        '''
        修改
        '''
        logger.info(sql)
        return self.connect_and_send(sql, 'update')


    def executer_delete(self, sql):
        '''
        删除
        '''
        logger.info(sql)
        return self.connect_and_send(sql, 'delete')


    def executer(self, sql):
        '''
        适用于建表、删表等语句
        '''
        logger.info(sql)
        return self.connect_and_send(sql)


    def set_transaction(self, started):
        if started:
            self.startTransaction = 'true'
        else:
            self.startTransaction = 'false'


    def init_socket_connection(self):
        client = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        client.setsockopt(socket.SOL_SOCKET, socket.SO_KEEPALIVE, 1)
        client.connect(('127.0.0.1', 8080))
        self.client = client


    def connect_and_send(self, sql, operate_type=''):
        config_data = {
            'sql': sql,
            'config': self.databaseConfig,
            'type': operate_type,
            'transaction': self.startTransaction
        }
        send_data = str(config_data)
        self.client.send(send_data.encode('utf-8'))

        if operate_type == 'query':
            buffer = []
            while True:
                recvdata = self.client.recv(1024)
                logger.info("in receiving data: " + str(len(recvdata)))
                if not recvdata or len(recvdata) == 0:
                    logger.info("receive empty break")
                    break
                else:
                    buffer.append(recvdata)
                    logger.info("receive length break:" + str(len(recvdata)))
                    if (len(recvdata) < 1024):
                        break
            logger.info("end receive data: " + str(len(buffer)))

            data = b''.join(buffer)
            result = str(data, encoding='utf-8')
            return json.loads(result)
        else:
            buffer = []
            d = self.client.recv(1024)
            buffer.append(d)
            data = b''.join(buffer)
            return str(data, encoding='utf-8')
